Testing ideas for the Shiny app/dashboard.

Background

Libraries

if (!require("pacman")) install.packages("pacman"); library(pacman)
## Loading required package: pacman
p_load(shiny,tidyverse, scales, rvest, janitor, plotly, readxl, MetBrewer)

Functions

HTML

read_proff6 <- function(file, start, end){
    read_html(file, encoding = "UTF-8") |> 
      html_node("#inner-frame") |> 
      html_table() |> 
      select( # removing empty column
        REGNSKAPSPERIODE:all_of(start)) |> # using all_of()
      mutate( # removing whitespace
        REGNSKAPSPERIODE = str_squish(REGNSKAPSPERIODE)
      ) |> 
      filter( # removing duplicate table - no idea why it's necessary
        !row_number() > 176
      ) |>
      filter( # removing duplicate rows
        !grepl("Lukk", REGNSKAPSPERIODE)
      ) |> 
      pivot_longer( # tidying data
        all_of(end):all_of(start), names_to = "year") |> # using all_of()
      mutate( # changing to real NAs and turning years into numbers
        value = na_if(value, "-"),
        year = as.integer(year)) |> 
      mutate( # adding currency column
        valutakode = ifelse(REGNSKAPSPERIODE == "Valutakode", value, NA)
      ) |> 
      fill( # filling the currency column
        valutakode, .direction = "updown"
      ) |> 
      filter( # removing dates and redundant currency
        REGNSKAPSPERIODE != "Sluttdato" & REGNSKAPSPERIODE != "Startdato" &
          REGNSKAPSPERIODE != "Valutakode") |> 
      mutate( # removing whitespace in numbers
        value = str_replace_all(value, "\\s", "")
      ) |> 
      mutate( # turning into numbers
        value = as.numeric(value)
      ) |> 
      mutate( # removing years as values
        value = ifelse(grepl("i hele 1000", REGNSKAPSPERIODE), NA, value)
      ) |> 
      mutate( # values no longer in 1000
        value = value * 1000
      ) |> 
      distinct() |> # removing any remaining duplicate rows
      pivot_wider( # pivoting
        names_from = REGNSKAPSPERIODE, values_from = value
      ) |> 
      clean_names() |> # tidying names
      select(-lederlonn_i_hele_1000,
             -resultatregnskap_i_hele_1000,
             -balanseregnskap_i_hele_1000) |> # removing headings
      rename( # making it clear what lonn refers to
        lederlonn = lonn
      ) |> 
      arrange(desc(year)) # arranging by year
}

Excel wide

widen_excel <- function(file){
  read_excel(file) |> 
    mutate(
      Value = Value * 1000
    ) |> 
  unite(
    entry, c(Category, Name), sep = "_"
  ) |> 
  select(-`Sub-category`) |> 
  pivot_wider(names_from = entry, values_from = Value) |> 
  clean_names()
}

Loading data

kreft_html <- read_proff6("Data/Kreftforeningen.html", "2002", "2021")
kreft_excel <- widen_excel("Data/OPXfinans_final.xlsx")

Viewing data

View()

View(kreft_html)
View(kreft_excel)

kabled

kreft_html
year valutakode lederlonn leder_annen_godtgjorelse sum_salgsinntekter annen_driftsinntekt sum_driftsinntekter varekostnad beholdningsendringer lonnskostnader herav_kun_lonn ordinaere_avskrivninger nedskrivning andre_driftskostnader driftsresultat inntekt_pa_invest_annet_foretak_i_sm_konsern inntekt_pa_investering_i_datterselskap sum_annen_renteinntekt inntekt_pa_invest_i_tilknyttet_selskap sum_annen_finansinntekt sum_finansinntekter nedskrivning_fin_anleggsmidler sum_annen_rentekostnad andre_finanskostnader sum_annen_finanskostnad sum_finanskostnader resultat_for_skatt sum_skatt ordinaert_resultat ekstraordinaere_inntekter ekstraordinaere_kostnader skatt_ekstraordinaert arsresultat ordinaert_utbytte ekstraordinaert_utbytte tilleggsutbytte sum_utbytte konsernbidrag goodwill sum_immaterielle_midler sum_anleggsmidler tomter_bygninger_og_annen_fast_eiendom maskiner_anlegg_biler driftslosore_inventar_verktoy_biler sum_varige_driftsmidler aksjer_investeringer_i_datterselskap endr_behold_varer_under_tilvirk_ferdige investeringer_i_aksjer_og_andeler andre_fordringer sum_finansielle_anleggsmidler sum_varelager kundefordringer konsernfordringer sum_fordringer sum_investeringer kasse_bank_post sum_kasse_bank_post sum_omlopsmidler sum_eiendeler aksje_selskapskapital annen_innskutt_egenkapital sum_innskutt_egenkapital sum_opptjent_egenkapital annen_egenkapital sum_egenkapital sum_avsetninger_til_forpliktelser pant_gjeld_til_kredittinstitusjoner langsiktig_konserngjeld ansvarlig_lanekapital sum_annen_langsiktig_gjeld annen_langsiktig_gjeld sum_langsiktig_gjeld gjeld_til_kredittinstitusjoner leverandorgjeld skyldig_offentlige_avgifter kortsiktig_konserngjeld annen_kortsiktig_gjeld sum_kortsiktig_gjeld sum_gjeld sum_egenkapital_og_gjeld garantistillelser pantstillelser
2021 NOK NA NA 27451000 505958000 533409000 103449000 NA NA NA NA NA 508048000 -78088000 NA NA NA NA NA 156648000 NA NA NA NA NA 78561000 NA 78561000 NA NA NA 78561000 NA NA NA NA NA NA NA 20533000 NA NA 14718000 14718000 150000 NA 35000 5630000 5815000 NA 6143000 NA 35528000 1423202000 238371000 238371000 1697102000 1717635000 1e+08 NA 100000000 909398000 909398000 1009398000 368635000 NA NA NA NA NA 368635000 NA 14563000 11428000 NA 313612000 339602000 708237000 1717635000 NA NA
2020 NOK NA NA 25087000 498713000 523800000 93587000 NA NA 115868000 NA NA 444959000 -14746000 NA NA NA NA NA 81052000 NA NA NA NA NA 66306000 NA 66306000 NA NA NA 66306000 NA NA NA NA NA NA NA 21506000 NA NA 15691000 15691000 150000 NA 35000 5630000 5815000 NA 11628000 NA 49205000 1217305000 271319000 271319000 1537829000 1559335000 1e+08 NA 100000000 830838000 830838000 930837000 311562000 NA NA NA NA NA 311562000 NA 12634000 10216000 NA 294085000 316936000 628498000 1559335000 NA NA
2019 NOK NA NA 24310000 532437000 556747000 91597000 NA NA 112039000 NA NA 440284000 24866000 NA NA NA NA NA 228726000 NA NA NA NA NA 253592000 NA 253592000 NA NA NA 253592000 NA NA NA NA NA NA NA 22309000 NA NA 16494000 16494000 150000 NA 35000 5630000 5815000 NA 11521000 NA 54847000 1137580000 231742000 231742000 1424170000 1446478000 1e+08 NA 100000000 764531000 764531000 864531000 310847000 NA NA NA NA NA 310847000 NA 10582000 9942000 NA 250577000 271101000 581948000 1446478000 3378000 NA
2018 NOK NA NA 27289000 519161000 546450000 88546000 NA NA 112606000 NA NA 454090000 3814000 NA NA NA NA NA -13506000 NA NA NA NA NA -9693000 NA -9693000 NA NA NA -9693000 NA NA NA NA NA NA NA 21854000 NA NA 21669000 21669000 150000 NA 35000 NA 185000 NA 15722000 NA 66629000 935205000 150494000 150494000 1152329000 1174182000 1e+08 NA 100000000 510939000 510939000 610939000 288223000 NA NA NA NA NA 288223000 NA 14834000 9847000 NA 250340000 275020000 563244000 1174182000 NA NA
2017 NOK 1505000 72000 23536000 496077000 519613000 85718000 NA NA 107077000 NA NA 469167000 -35272000 NA NA NA NA NA 94862000 NA NA NA NA 0 59589000 NA 59589000 NA NA NA NA NA NA NA NA NA NA NA 22920000 NA NA 22735000 22735000 150000 NA 35000 NA 185000 NA 12131000 NA 34029000 990189000 130097000 130097000 1154316000 1177236000 1e+08 NA 100000000 520632000 520632000 620632000 263783000 NA NA NA NA NA 263783000 NA 10376000 9728000 NA 272717000 292821000 556604000 1177236000 NA NA
2016 NOK 1437000 47000 22755000 482634000 505389000 79496000 NA NA 108231000 NA NA 490831000 -64938000 NA NA NA NA NA 55987000 NA NA NA NA 0 -8952000 NA -8952000 NA NA NA NA NA NA NA NA NA NA NA 17641000 814000 NA 16642000 17456000 150000 NA 35000 NA 185000 NA 15676000 NA 19934000 951779000 134479000 134479000 1106192000 1123833000 1e+08 NA 100000000 453860000 453860000 553860000 525846000 NA NA NA NA NA 525846000 NA 17028000 10214000 NA 16885000 44127000 569973000 1123833000 NA NA
2015 NOK 1363000 40000 23232000 476820000 500052000 83519000 NA NA 105407000 NA NA 505227000 -88694000 NA NA NA NA NA 39781000 NA NA NA NA 0 -48914000 NA -48914000 NA NA NA NA NA NA NA NA NA NA NA 17010000 814000 NA 16011000 16825000 150000 NA 35000 NA 185000 NA 10882000 NA 17167000 901854000 201194000 201194000 1120215000 1137225000 1e+08 NA 100000000 462811000 462811000 562812000 533370000 NA NA NA NA NA 533370000 NA 15284000 9407000 NA 16353000 41043000 574413000 1137225000 NA NA
2014 NOK 1292000 15000 23719000 453171000 476890000 68641000 NA NA 96612000 NA NA 479130000 -70881000 NA NA NA NA NA 74519000 NA NA NA NA 0 3637000 NA 3637000 NA NA NA NA NA NA NA NA NA NA NA 9420000 814000 NA 8421000 9235000 150000 NA 35000 NA 185000 NA 16569000 NA 21411000 894542000 211626000 211626000 1127579000 1136999000 1e+08 NA 100000000 511726000 511726000 611726000 487611000 NA NA NA NA NA 487611000 NA 10741000 9788000 NA 17134000 37662000 525273000 1136999000 NA NA
2013 NOK 1262000 36000 20627000 415352000 435979000 67069000 NA NA 91847000 NA NA 456530000 -87620000 NA NA NA NA NA 90198000 NA NA NA NA 0 2578000 NA 2578000 NA NA NA NA NA NA NA NA NA NA NA 10481000 814000 NA 9482000 10296000 150000 NA 35000 NA 185000 NA 14431000 NA 18108000 863275000 188552000 188552000 1069936000 1080417000 1e+08 NA 100000000 508089000 508089000 608089000 430391000 NA NA NA NA NA 430391000 NA 9170000 9471000 NA 23296000 41938000 472329000 1080417000 NA NA
2012 NOK 1237000 35000 21045000 395441000 416486000 64434000 NA NA 93625000 NA NA 414992000 -62940000 NA NA NA NA NA 73723000 NA NA NA NA 0 10781000 NA 10781000 NA NA NA NA NA NA NA NA NA NA NA 11153000 814000 NA 10154000 10968000 150000 NA 35000 NA 185000 NA 14936000 NA 20183000 775659000 201486000 201486000 997328000 1008481000 1e+08 NA 100000000 505511000 505511000 605510000 362848000 NA NA NA NA NA 362848000 NA 6076000 9765000 NA 24282000 40123000 402971000 1008481000 NA NA
2011 NOK NA NA 20550000 375158000 395708000 70966000 NA NA NA NA NA 381303000 -56561000 NA NA NA NA NA -48265000 NA NA NA NA 0 -104826000 NA -104826000 NA NA NA -104826000 NA NA NA NA NA NA NA 11540000 814000 NA 10541000 11355000 150000 NA 35000 NA 185000 NA 12336000 NA 19275000 705586000 138595000 138595000 863456000 874996000 1e+08 NA 100000000 494729000 494729000 594729000 243204000 NA NA NA NA NA 243204000 NA 10856000 8772000 NA 17436000 37064000 280268000 874996000 NA NA
2010 NOK NA NA 18898000 394270000 413168000 59821000 NA NA NA NA NA 319848000 33499000 NA NA NA NA NA 101511000 NA NA NA NA 0 135010000 NA 135010000 NA NA NA 135010000 NA NA NA NA NA NA NA 13252000 814000 NA 12253000 13067000 NA NA NA 185000 185000 106000 NA NA 40719000 817283000 134792000 134792000 992900000 1006152000 1e+08 NA 100000000 599555000 599555000 699555000 271353000 NA NA NA NA NA 271353000 NA 8796000 8228000 NA 18220000 35245000 306598000 1006152000 NA NA
2009 NOK 1154000 44000 17115000 483182000 500297000 0 0 0 78694000 0 NA 416120000 84177000 0 0 0 0 0 0 NA 0 NA 0 0 84177000 0 84177000 NA NA 0 84177000 NA NA NA NA NA NA 0 26970000 1189000 NA 15546000 16735000 NA 0 NA 10235000 10235000 0 NA NA 20268000 749521000 104376000 104376000 874165000 901135000 NA 100000000 100000000 464545000 464545000 564545000 289251000 NA NA NA 0 NA 289251000 NA 16133000 7846000 NA 23361000 47340000 336590000 901135000 NA NA
2008 NOK NA NA 16917000 115787000 132704000 0 0 0 73252000 0 NA 380021000 -247317000 0 0 0 0 0 0 NA 0 NA 0 0 -247317000 0 -247317000 NA NA 0 -247317000 NA NA NA NA NA NA 0 33920000 2727000 NA 16185000 18912000 NA 0 NA 15008000 15008000 0 NA NA 44538000 667011000 73102000 73102000 784651000 818571000 NA 123481000 123481000 356887000 356887000 480368000 290048000 NA NA NA 0 NA 290048000 NA 14645000 7249000 NA 26262000 48156000 338203000 818571000 NA NA
2007 NOK 936000 52000 86715000 345348000 432062000 0 0 0 85553000 0 NA 168152000 263910000 0 0 0 0 0 0 NA 0 150438000 150438000 150438000 113472000 0 113472000 NA NA 0 113472000 NA NA NA NA NA NA 0 29579000 540000 NA 13346000 13886000 NA 0 NA 15693000 15693000 0 NA NA 50797000 941052000 70731000 70731000 1062580000 1092159000 NA 125902000 125902000 601783000 601783000 727685000 324319000 NA NA NA 0 NA 324319000 NA 8376000 6850000 NA 24930000 40155000 364474000 1092159000 NA NA
2004 NOK 896000 NA 1484000 364926000 366410000 1805000 NA 177032000 150280000 3143000 NA 121399000 63031000 NA NA NA NA 7713000 7713000 NA NA NA 0 -42762000 113506000 NA 113506000 NA NA NA 113506000 NA NA NA NA NA NA 0 136818000 3340000 NA 11273000 14613000 NA NA NA NA 122205000 647000 0 NA 6113000 545950000 111010000 NA 663720000 800538000 1e+08 NA 100000000 483604000 483604000 583604000 155467000 NA NA NA 155467000 NA 155467000 NA 14621000 29038000 NA 17808000 61467000 216934000 800538000 NA NA
2003 NOK NA NA NA 352040000 352040000 NA NA 172320000 NA 2268000 NA 138773000 38679000 NA NA NA NA 9512000 9512000 NA NA NA 972000 -69550000 117741000 NA 117741000 NA NA NA 117741000 NA NA NA NA NA NA 0 122795000 3068000 NA 7888000 10956000 NA NA NA NA 111839000 1441000 0 NA 2439000 448556000 103252000 NA 555688000 678483000 1e+08 NA 100000000 370097000 370097000 470097000 151041000 NA NA NA 151041000 NA 151041000 NA 14285000 26523000 NA 16537000 57345000 208386000 678483000 NA NA
2002 NOK 1023000 NA 5935000 360855000 366790000 4352000 NA 169552000 144086000 2935000 NA 143380000 46571000 NA NA NA NA 2708000 2708000 NA NA NA 304000 68115000 -18836000 0 -18836000 NA NA NA -18836000 NA NA NA NA NA NA 0 123788000 3068000 NA 4356000 7424000 NA NA NA NA 116364000 2222000 0 NA 9154000 348668000 75658000 NA 435702000 559490000 1e+08 NA 100000000 252356000 252356000 352356000 151298000 NA NA NA 151298000 NA 151298000 NA 9104000 28167000 NA 18565000 55836000 207134000 559490000 NA NA
kreft_excel
year firm revenue_private_contributions revenue_public_grants revenue_operational_activities revenue_membership_fees revenue_commercial_activities revenue_investments revenue_other expenses_program_services expenses_administrative expenses_fundraising expenses_other assets_property_plant_equipment_and_intangible_assets assets_long_term_investments assets_long_term_receivables assets_other_non_current_assets assets_cash_in_bank_and_cash_equivalents assets_short_term_receivables assets_short_term_investments assets_other_current_assets liability_short_term_grants_payable liability_revocable_endowments liability_other_current_liabilitues liability_long_term_grants_payable liability_other_non_current_liabilities equity_equity
2020 Kreftforeningen 462196000 29148000 4806000 25087000 0 81052000 2563000 434431000 10528000 93587000 1000 15691000 185000 5630000 0 0 271319000 48408000 1218102000 272786000 7498000 36650000 301822000 9741000 930837000
2019 Kreftforeningen 496545000 28570000 4574000 24309000 0 228726000 2748000 430086000 10198000 91596000 1000 16494000 185000 5630000 0 0 231742000 54012000 1138415000 233486000 2313000 35302000 301842000 9005000 864530000

Testing

Data manipulation

kreft_excel |> 
  filter(year == max(year))
year firm revenue_private_contributions revenue_public_grants revenue_operational_activities revenue_membership_fees revenue_commercial_activities revenue_investments revenue_other expenses_program_services expenses_administrative expenses_fundraising expenses_other assets_property_plant_equipment_and_intangible_assets assets_long_term_investments assets_long_term_receivables assets_other_non_current_assets assets_cash_in_bank_and_cash_equivalents assets_short_term_receivables assets_short_term_investments assets_other_current_assets liability_short_term_grants_payable liability_revocable_endowments liability_other_current_liabilitues liability_long_term_grants_payable liability_other_non_current_liabilities equity_equity
2020 Kreftforeningen 462196000 29148000 4806000 25087000 0 81052000 2563000 434431000 10528000 93587000 1000 15691000 185000 5630000 0 0 271319000 48408000 1218102000 272786000 7498000 36650000 301822000 9741000 930837000
kreft_html |> 
  filter(year == max(kreft_excel$year))
year valutakode lederlonn leder_annen_godtgjorelse sum_salgsinntekter annen_driftsinntekt sum_driftsinntekter varekostnad beholdningsendringer lonnskostnader herav_kun_lonn ordinaere_avskrivninger nedskrivning andre_driftskostnader driftsresultat inntekt_pa_invest_annet_foretak_i_sm_konsern inntekt_pa_investering_i_datterselskap sum_annen_renteinntekt inntekt_pa_invest_i_tilknyttet_selskap sum_annen_finansinntekt sum_finansinntekter nedskrivning_fin_anleggsmidler sum_annen_rentekostnad andre_finanskostnader sum_annen_finanskostnad sum_finanskostnader resultat_for_skatt sum_skatt ordinaert_resultat ekstraordinaere_inntekter ekstraordinaere_kostnader skatt_ekstraordinaert arsresultat ordinaert_utbytte ekstraordinaert_utbytte tilleggsutbytte sum_utbytte konsernbidrag goodwill sum_immaterielle_midler sum_anleggsmidler tomter_bygninger_og_annen_fast_eiendom maskiner_anlegg_biler driftslosore_inventar_verktoy_biler sum_varige_driftsmidler aksjer_investeringer_i_datterselskap endr_behold_varer_under_tilvirk_ferdige investeringer_i_aksjer_og_andeler andre_fordringer sum_finansielle_anleggsmidler sum_varelager kundefordringer konsernfordringer sum_fordringer sum_investeringer kasse_bank_post sum_kasse_bank_post sum_omlopsmidler sum_eiendeler aksje_selskapskapital annen_innskutt_egenkapital sum_innskutt_egenkapital sum_opptjent_egenkapital annen_egenkapital sum_egenkapital sum_avsetninger_til_forpliktelser pant_gjeld_til_kredittinstitusjoner langsiktig_konserngjeld ansvarlig_lanekapital sum_annen_langsiktig_gjeld annen_langsiktig_gjeld sum_langsiktig_gjeld gjeld_til_kredittinstitusjoner leverandorgjeld skyldig_offentlige_avgifter kortsiktig_konserngjeld annen_kortsiktig_gjeld sum_kortsiktig_gjeld sum_gjeld sum_egenkapital_og_gjeld garantistillelser pantstillelser
2020 NOK NA NA 25087000 498713000 523800000 93587000 NA NA 115868000 NA NA 444959000 -14746000 NA NA NA NA NA 81052000 NA NA NA NA NA 66306000 NA 66306000 NA NA NA 66306000 NA NA NA NA NA NA NA 21506000 NA NA 15691000 15691000 150000 NA 35000 5630000 5815000 NA 11628000 NA 49205000 1217305000 271319000 271319000 1537829000 1559335000 1e+08 NA 1e+08 830838000 830838000 930837000 311562000 NA NA NA NA NA 311562000 NA 12634000 10216000 NA 294085000 316936000 628498000 1559335000 NA NA
kreft_excel |> 
  left_join(kreft_html, by = "year")
year firm revenue_private_contributions revenue_public_grants revenue_operational_activities revenue_membership_fees revenue_commercial_activities revenue_investments revenue_other expenses_program_services expenses_administrative expenses_fundraising expenses_other assets_property_plant_equipment_and_intangible_assets assets_long_term_investments assets_long_term_receivables assets_other_non_current_assets assets_cash_in_bank_and_cash_equivalents assets_short_term_receivables assets_short_term_investments assets_other_current_assets liability_short_term_grants_payable liability_revocable_endowments liability_other_current_liabilitues liability_long_term_grants_payable liability_other_non_current_liabilities equity_equity valutakode lederlonn leder_annen_godtgjorelse sum_salgsinntekter annen_driftsinntekt sum_driftsinntekter varekostnad beholdningsendringer lonnskostnader herav_kun_lonn ordinaere_avskrivninger nedskrivning andre_driftskostnader driftsresultat inntekt_pa_invest_annet_foretak_i_sm_konsern inntekt_pa_investering_i_datterselskap sum_annen_renteinntekt inntekt_pa_invest_i_tilknyttet_selskap sum_annen_finansinntekt sum_finansinntekter nedskrivning_fin_anleggsmidler sum_annen_rentekostnad andre_finanskostnader sum_annen_finanskostnad sum_finanskostnader resultat_for_skatt sum_skatt ordinaert_resultat ekstraordinaere_inntekter ekstraordinaere_kostnader skatt_ekstraordinaert arsresultat ordinaert_utbytte ekstraordinaert_utbytte tilleggsutbytte sum_utbytte konsernbidrag goodwill sum_immaterielle_midler sum_anleggsmidler tomter_bygninger_og_annen_fast_eiendom maskiner_anlegg_biler driftslosore_inventar_verktoy_biler sum_varige_driftsmidler aksjer_investeringer_i_datterselskap endr_behold_varer_under_tilvirk_ferdige investeringer_i_aksjer_og_andeler andre_fordringer sum_finansielle_anleggsmidler sum_varelager kundefordringer konsernfordringer sum_fordringer sum_investeringer kasse_bank_post sum_kasse_bank_post sum_omlopsmidler sum_eiendeler aksje_selskapskapital annen_innskutt_egenkapital sum_innskutt_egenkapital sum_opptjent_egenkapital annen_egenkapital sum_egenkapital sum_avsetninger_til_forpliktelser pant_gjeld_til_kredittinstitusjoner langsiktig_konserngjeld ansvarlig_lanekapital sum_annen_langsiktig_gjeld annen_langsiktig_gjeld sum_langsiktig_gjeld gjeld_til_kredittinstitusjoner leverandorgjeld skyldig_offentlige_avgifter kortsiktig_konserngjeld annen_kortsiktig_gjeld sum_kortsiktig_gjeld sum_gjeld sum_egenkapital_og_gjeld garantistillelser pantstillelser
2020 Kreftforeningen 462196000 29148000 4806000 25087000 0 81052000 2563000 434431000 10528000 93587000 1000 15691000 185000 5630000 0 0 271319000 48408000 1218102000 272786000 7498000 36650000 301822000 9741000 930837000 NOK NA NA 25087000 498713000 523800000 93587000 NA NA 115868000 NA NA 444959000 -14746000 NA NA NA NA NA 81052000 NA NA NA NA NA 66306000 NA 66306000 NA NA NA 66306000 NA NA NA NA NA NA NA 21506000 NA NA 15691000 15691000 150000 NA 35000 5630000 5815000 NA 11628000 NA 49205000 1217305000 271319000 271319000 1537829000 1559335000 1e+08 NA 1e+08 830838000 830838000 930837000 311562000 NA NA NA NA NA 311562000 NA 12634000 10216000 NA 294085000 316936000 628498000 1559335000 NA NA
2019 Kreftforeningen 496545000 28570000 4574000 24309000 0 228726000 2748000 430086000 10198000 91596000 1000 16494000 185000 5630000 0 0 231742000 54012000 1138415000 233486000 2313000 35302000 301842000 9005000 864530000 NOK NA NA 24310000 532437000 556747000 91597000 NA NA 112039000 NA NA 440284000 24866000 NA NA NA NA NA 228726000 NA NA NA NA NA 253592000 NA 253592000 NA NA NA 253592000 NA NA NA NA NA NA NA 22309000 NA NA 16494000 16494000 150000 NA 35000 5630000 5815000 NA 11521000 NA 54847000 1137580000 231742000 231742000 1424170000 1446478000 1e+08 NA 1e+08 764531000 764531000 864531000 310847000 NA NA NA NA NA 310847000 NA 10582000 9942000 NA 250577000 271101000 581948000 1446478000 3378000 NA

Visualisation ideas

CEO compensation and currency testing

compensation_p <- kreft_html |> 
  group_by(year) |> 
  mutate(
    leder_total = sum(lederlonn, leder_annen_godtgjorelse, na.rm = TRUE)
  ) |> 
  ggplot(aes(year, leder_total, fill = leder_total)) +
  geom_col() +
  scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
ggplotly(compensation_p)
compensation_p2 <- kreft_html |> 
  select(
    year, lederlonn, leder_annen_godtgjorelse
  ) |> 
  rename(
    Year = year,
    Salary = lederlonn,
    `Other compensation` = leder_annen_godtgjorelse
  ) |> 
  pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |> 
  ggplot(aes(Year, value, fill = Compensation)) +
  geom_col() +
  scale_y_continuous(
    labels = scales::label_dollar(prefix =
                                    str_c(kreft_html$valutakode[1], " "))) +
  scale_fill_met_d("Troy") +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
ggplotly(compensation_p2)
## Warning: Removed 18 rows containing missing values (position_stack).
# if else structure to check for uniform currency code?
length(unique(kreft_html$valutakode)) == 1
## [1] TRUE
# Testing with uniform currency
if (length(unique(kreft_html$valutakode)) == 1) {
  kreft_html |> 
  select(
    year, lederlonn, leder_annen_godtgjorelse
  ) |> 
  rename(
    Year = year,
    Salary = lederlonn,
    `Other compensation` = leder_annen_godtgjorelse
  ) |> 
  pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |> 
  ggplot(aes(Year, value, fill = Compensation)) +
  geom_col() +
  scale_y_continuous(
    labels = scales::label_dollar(prefix =
                                    str_c(kreft_html$valutakode[1], " "))) +
  scale_fill_met_d("Troy") +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
} else {
    kreft_html |> 
  select(
    year, lederlonn, leder_annen_godtgjorelse
  ) |> 
  rename(
    Year = year,
    Salary = lederlonn,
    `Other compensation` = leder_annen_godtgjorelse
  ) |> 
  pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |> 
  ggplot(aes(Year, value, fill = Compensation)) +
  geom_col() +
  scale_y_continuous(
    labels = scales::label_dollar(prefix = "? ")) +
  scale_fill_met_d("Troy") +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
       subtitle = "Note: not all values are denominated in the same currency")
}
## Warning: Removed 18 rows containing missing values (position_stack).

# Creating data frame with multiple currencies
currency_test <- kreft_html |> 
  mutate(
    valutakode = ifelse(year == 2019, "USD", valutakode)
  )

# Testing new data frame
if (length(unique(currency_test$valutakode)) == 1) {
  currency_test |> 
  select(
    year, lederlonn, leder_annen_godtgjorelse
  ) |> 
  rename(
    Year = year,
    Salary = lederlonn,
    `Other compensation` = leder_annen_godtgjorelse
  ) |> 
  pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |> 
  ggplot(aes(Year, value, fill = Compensation)) +
  geom_col() +
  scale_y_continuous(
    labels = scales::label_dollar(prefix =
                                    str_c(kreft_html$valutakode[1], " "))) +
  scale_fill_met_d("Troy") +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
} else {
    currency_test |> 
  select(
    year, lederlonn, leder_annen_godtgjorelse
  ) |> 
  rename(
    Year = year,
    Salary = lederlonn,
    `Other compensation` = leder_annen_godtgjorelse
  ) |> 
  pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |> 
  ggplot(aes(Year, value, fill = Compensation)) +
  geom_col() +
  scale_y_continuous(
    labels = scales::label_dollar(prefix = "? ")) +
  scale_fill_met_d("Troy") +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
       subtitle = "Note: not all values are denominated in the same currency")
}
## Warning: Removed 18 rows containing missing values (position_stack).

# Could probably be done with two internal ifelse() functions

# ifelse() version

# Testing
currency_test |> 
  select(
    year, lederlonn, leder_annen_godtgjorelse
  ) |> 
  rename(
    Year = year,
    Salary = lederlonn,
    `Other compensation` = leder_annen_godtgjorelse
  ) |> 
  pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |> 
  ggplot(aes(Year, value, fill = Compensation)) +
  geom_col() +
  scale_y_continuous(
    labels = ifelse(length(unique(currency_test$valutakode)) == 1,
                    label_dollar(prefix = str_c(kreft_html$valutakode[1], " ")),
                    label_dollar(prefix = "? "))
      ) +
  scale_fill_met_d("Troy") +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
       subtitle = ifelse(length(unique(currency_test$valutakode)) == 1,
                         element_blank(),
                         "Note: not all values are denominated in the same currency"))
## Warning: Removed 18 rows containing missing values (position_stack).

# Original
kreft_html |> 
  select(
    year, lederlonn, leder_annen_godtgjorelse
  ) |> 
  rename(
    Year = year,
    Salary = lederlonn,
    `Other compensation` = leder_annen_godtgjorelse
  ) |> 
  pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |> 
  ggplot(aes(Year, value, fill = Compensation)) +
  geom_col() +
  scale_y_continuous(
    labels = ifelse(length(unique(kreft_html$valutakode)) == 1,
                    label_dollar(prefix = str_c(kreft_html$valutakode[1], " ")),
                    label_dollar(prefix = "? "))
      ) +
  scale_fill_met_d("Troy") +
  labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
       subtitle = ifelse(length(unique(kreft_html$valutakode)) == 1,
                         "",
                         "Note: not all values are denominated in the same currency"))
## Warning: Removed 18 rows containing missing values (position_stack).

Result

arsresultat_p <- kreft_html |> 
  mutate(
    profit = ifelse(arsresultat >= 0, TRUE, FALSE)
  ) |> 
  ggplot(aes(year, arsresultat, fill = profit)) +
  geom_col() +
  scale_y_continuous(labels = scales::label_dollar(prefix = ""))
ggplotly(arsresultat_p)
## Warning: Removed 6 rows containing missing values (position_stack).
arsresultat_p_met <- kreft_html |> 
  mutate(
    profit = ifelse(arsresultat >= 0, TRUE, FALSE)
  ) |> 
  ggplot(aes(year, arsresultat, fill = profit)) +
  geom_col() +
  scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
  scale_fill_met_d("Tsimshian")
ggplotly(arsresultat_p_met)
## Warning: Removed 6 rows containing missing values (position_stack).
ordinaert_p <- kreft_html |> 
  mutate(
    profit = ifelse(ordinaert_resultat >= 0, TRUE, FALSE)
  ) |> 
  ggplot(aes(year, ordinaert_resultat, fill = profit)) +
  geom_col() +
  scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
  theme(legend.position = "none")
ggplotly(ordinaert_p)
ordinaert_p_met <- kreft_html |> 
  mutate(
    profit = ifelse(ordinaert_resultat >= 0, TRUE, FALSE)
  ) |> 
  ggplot(aes(year, ordinaert_resultat, fill = profit)) +
  geom_col() +
  scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
  theme(legend.position = "none") +
  scale_fill_met_d("Tsimshian")
ggplotly(ordinaert_p_met)

KPI ideas

Fundraising ROI

kreft_excel |> 
  mutate(
    KPI_ROI = expenses_fundraising / (revenue_private_contributions + revenue_membership_fees)
  ) |> 
  select(year, KPI_ROI)
year KPI_ROI
2020 0.1920588
2019 0.1758573

Administrative expenses

kreft_excel |> 
  mutate(
    KPI_admin =
      str_c(
        format(
        (expenses_administrative / expenses_program_services *100), digits = 3
        ), "%"
        )
  ) |> 
  select(year, KPI_admin)
year KPI_admin
2020 2.42%
2019 2.37%
kreft_excel |> 
  mutate(
    KPI_admin =
      str_c(
        format(
        (expenses_administrative / expenses_program_services *100), digits = 3
        ), "%"
        )
  ) |> 
  filter(year == max(year)) |> 
  select(KPI_admin) |> 
  str_flatten()
## [1] "2.42%"
kreft_excel |> mutate(
            KPI_admin =
              expenses_administrative / expenses_program_services *100) |> 
           filter(year == max(year)) |> 
          select(KPI_admin)
KPI_admin
2.4234

Overhead